Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Collections.Generic
Imports Microsoft.VisualBasic
Imports nsDataProvider = KDESolutions.Providers.DataProvider
Imports System.Collections.Specialized
Imports System
Imports System.Configuration
Imports System.Diagnostics
Imports System.Web
Imports System.Globalization
Imports System.Security.Cryptography
Imports System.Text

Public Class DBArticlesProvider
    Inherits ArticlesProvider

    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String

    Public Const cAccessTrueValue As String = "Y"
    Public Const cAccessFalsevalue As String = "N"

    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property

    Public Overrides ReadOnly Property Name() As String
        Get
            Return "KDESolutions.Providers.Articles.DBArticlesProvider"
        End Get
    End Property

    Public Overrides ReadOnly Property Description() As String
        Get
            Return "Provider for storing articles"
        End Get
    End Property

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If

        If ((name Is Nothing) OrElse (name.Length = 0)) Then
            name = "ArticleProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "KDESolutions Article provider")
        End If

        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty("DataProviderGroup") Then
            Throw New ArgumentNullException("You must provide a DataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("DataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If

    End Sub

    ' Returns a collection with all the categories
    Public Overrides Function GetCategories() As List(Of DataTypes.CategoryDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT CategoryID, AddedDate, AddedBy, Title, Importance, Description, ImageUrl " & _
        "FROM ArticleCategories WHERE LogicalDeleteInd = ? ORDER BY Importance DESC, Title ASC"
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return GetCategoryCollectionFromReader(DAP.ExecuteReader(strSQL))
    End Function

    ' Returns an existing category with the specified ID
    Public Overrides Function GetCategoryByID(ByVal categoryID As String) As DataTypes.CategoryDetails
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT CategoryID, AddedDate, AddedBy, Title, Importance, Description, ImageUrl " & _
        "FROM ArticleCategories WHERE CategoryID = ?"
        DAP.AddParameter("@CategoryID", categoryID, DbType.Int16)
        Dim reader As IDataReader = DAP.ExecuteReader(strSQL)
        If reader.Read() Then
            Return GetCategoryFromReader(reader)
        Else
            Return Nothing
        End If
    End Function

    ' Deletes a category
    Public Overrides Function DeleteCategory(ByVal categoryID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "Update ArticleCategories SET LogicalDeleteInd = ? WHERE CategoryID = ?"
        DAP.AddParameter("@LogicalDeleteInd", cAccessTrueValue, DbType.String)
        DAP.AddParameter("@CategoryID", categoryID, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    ' Updates a category
    Public Overrides Function UpdateCategory(ByVal category As DataTypes.CategoryDetails) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "UPDATE ArticleCategories " & _
                                "SET Title = ?, Importance = ?, Description = ?, " & _
                                "ImageUrl = ? " & _
                                "WHERE CategoryID = ?"
        DAP.AddParameter("@Title", category.Title, DbType.String)
        DAP.AddParameter("@Importance", category.Importance, DbType.Int16)
        DAP.AddParameter("@Description", category.Description, DbType.String)
        DAP.AddParameter("@ImageUrl", category.ImageURL, DbType.String)
        DAP.AddParameter("@CategoryID", category.CategoryId, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    ' creates a new category
    Public Overrides Function InsertCategory(ByVal category As DataTypes.CategoryDetails) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sNewCategoryId As String = System.Guid.NewGuid.ToString
        Dim strSQL As String = "INSERT INTO ArticleCategories(CategoryId, AddedDate, AddedBy, Title, Importance, " & _
        "Description, ImageUrl, LogicalDeleteInd) " & _
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
        DAP.AddParameter("@CategoryID", sNewCategoryId, DbType.String)
        DAP.AddParameter("@AddedDate", category.AddedDate, DbType.DateTime)
        DAP.AddParameter("@AddedBy", category.AddedBy, DbType.String)
        DAP.AddParameter("@Title", category.Title, DbType.String)
        DAP.AddParameter("@Importance", category.Importance, DbType.Int16)
        If category.Description Is Nothing Then
            DAP.AddParameter("@Description", String.Empty, DbType.String)
        Else
            DAP.AddParameter("@Description", category.Description, DbType.String)
        End If
        DAP.AddParameter("@ImageUrl", category.ImageURL, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return sNewCategoryId
    End Function

    ' retreives all articles
    Public Overrides Function GetArticles(ByVal pageIndex As Integer, ByVal pageSize As Integer) As List(Of DataTypes.ArticleDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT Articles.ArticleID, Articles.AddedDate, Articles.AddedBy, Articles.CategoryID, Articles.Title, Articles.Abstract, Articles.Body, " & _
       "Articles.Country, Articles.State, Articles.City, Articles.ReleaseDate, Articles.ExpireDate, Articles.Approved, " & _
       "Articles.Listed, Articles.CommentsEnabled, Articles.OnlyForMembers, Articles.ViewCount, Articles.Votes, Articles.TotalRating, " & _
       "ArticleCategories.Title AS CategoryTitle " & _
       "FROM Articles INNER JOIN " & _
       "ArticleCategories ON Articles.CategoryID = ArticleCategories.CategoryID WHERE Articles.LogicalDeleteInd = 'N' " & _
       "ORDER BY ReleaseDate DESC"
        Return GetArticleCollectionFromReader(DAP.ExecuteReader(strSQL), False)
    End Function

    ' Returns the total number of articles
    Public Overrides Function GetArticleCount() As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT COUNT(*) FROM Articles Where LogicalDeleteInd = ?"
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return CInt(DAP.ExecuteScalar(strSQL))
    End Function

    ' Retrieves all articles for the specified category
    Public Overrides Function GetArticles(ByVal categoryID As String, ByVal pageIndex As Integer, ByVal pageSize As Integer) As System.Collections.Generic.List(Of DataTypes.ArticleDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT Articles.ArticleID, Articles.AddedDate, Articles.AddedBy, Articles.CategoryID, Articles.Title, Articles.Abstract, Articles.Body, " & _
        "Articles.Country, Articles.State, Articles.City, Articles.ReleaseDate, Articles.ExpireDate, Articles.Approved, " & _
        "Articles.Listed, Articles.CommentsEnabled, Articles.OnlyForMembers, Articles.ViewCount, Articles.Votes, Articles.TotalRating, " & _
        "ArticleCategories.Title AS CategoryTitle " & _
        "FROM Articles INNER JOIN " & _
        "ArticleCategories ON Articles.CategoryID = ArticleCategories.CategoryID " & _
        "WHERE Articles.CategoryID = ? AND Articles.LogicalDeleteInd = ?" & _
        "ORDER BY ReleaseDate DESC"
        DAP.AddParameter("@CategoryID", categoryID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return GetArticleCollectionFromReader(DAP.ExecuteReader(strSQL), False)
    End Function

    ' Returns the total number of articles for the specified category
    Public Overrides Function GetArticleCount(ByVal categoryID As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT COUNT(*) FROM Articles WHERE CategoryID = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@CategoryID", categoryID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return CInt(DAP.ExecuteScalar(strSQL))
    End Function

    ' Retrieves all published articles
    Public Overrides Function GetPublishedArticles(ByVal currentDate As Date, ByVal pageIndex As Integer, ByVal pageSize As Integer) As List(Of DataTypes.ArticleDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT Articles.ArticleID, Articles.AddedDate, Articles.AddedBy, Articles.CategoryID, Articles.Title, Articles.Abstract, Articles.Body, " & _
      "Articles.Country, Articles.State, Articles.City, Articles.ReleaseDate, Articles.ExpireDate, Articles.Approved, " & _
      "Articles.Listed, Articles.CommentsEnabled, Articles.OnlyForMembers, Articles.ViewCount, Articles.Votes, Articles.TotalRating, " & _
      "ArticleCategories.Title AS CategoryTitle " & _
      "FROM Articles INNER JOIN " & _
      "ArticleCategories ON Articles.CategoryID = ArticleCategories.CategoryID " & _
      "WHERE Approved = 1 AND Listed = 1 AND " & _
      "ReleaseDate <= ? AND ExpireDate > ? ORDER BY ReleaseDate DESC"
        DAP.AddParameter("@ReleaseDate", currentDate, DbType.DateTime)
        DAP.AddParameter("@ExpireDate", currentDate, DbType.DateTime)
        Return GetArticleCollectionFromReader(DAP.ExecuteReader(strSQL), False)
    End Function

    ' Returns the total number of published articles
    Public Overrides Function GetPublishedArticleCount(ByVal currentDate As Date) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT COUNT(*) FROM Articles" & _
        "WHERE Approved = 1 AND Listed = 1 AND " & _
        "ReleaseDate <= ? AND ExpireDate > ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@CurrentDate", currentDate, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return CInt(DAP.ExecuteScalar(strSQL))
    End Function

    ' Retrieves all published articles for the specified category
    Public Overrides Function GetPublishedArticles(ByVal categoryID As String, ByVal currentDate As Date, _
        ByVal pageIndex As Integer, ByVal pageSize As Integer) As List(Of DataTypes.ArticleDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT Articles.ArticleID, Articles.AddedDate, Articles.AddedBy, Articles.CategoryID, Articles.Title, Articles.Abstract, Articles.Body," & _
        "Articles.Country, Articles.State, Articles.City, Articles.ReleaseDate, Articles.ExpireDate, Articles.Approved, " & _
        "Articles.Listed, Articles.CommentsEnabled, Articles.OnlyForMembers, Articles.ViewCount, Articles.Votes, Articles.TotalRating, " & _
        "ArticleCategories.Title AS CategoryTitle " & _
        "FROM Articles INNER JOIN " & _
        "ArticleCategories ON Articles.CategoryID = ArticleCategories.CategoryID " & _
        "WHERE Articles.Approved = 'Y' AND Articles.Listed = 'Y' AND " & _
        "Articles.ReleaseDate <= ? AND Articles.ExpireDate > ? " & _
        "AND Articles.CategoryID = ? " & _
        "ORDER BY ReleaseDate DESC"
        DAP.AddParameter("@CurrentDate", currentDate, DbType.DateTime)
        DAP.AddParameter("@ExpireDate", currentDate, DbType.DateTime)
        DAP.AddParameter("@CategoryID", categoryID, DbType.String)
        Return GetArticleCollectionFromReader(DAP.ExecuteReader(strSQL), False)
    End Function

    ' Returns the total number of published articles for the specified category
    Public Overrides Function GetPublishedArticleCount(ByVal categoryID As String, ByVal currentDate As Date) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT COUNT(*) FROM Articles" & _
        "WHERE Approved = 1 AND Listed = 1 AND " & _
        "ReleaseDate <= @CurrentDate AND ExpireDate > @CurrentDate " & _
        "AND CategoryID = @CategoryID AND LogicalDeleteInd = ?"
        DAP.AddParameter("@CategoryID", categoryID, DbType.String)
        DAP.AddParameter("@CurrentDate", currentDate, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return CInt(DAP.ExecuteScalar(strSQL))
    End Function

    ' Retrieves the article with the specified ID
    Public Overrides Function GetArticleByID(ByVal articleID As String) As DataTypes.ArticleDetails
        If Not articleID Is Nothing AndAlso articleID <> String.Empty Then
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim strSQL As String = "SELECT Articles.ArticleID, Articles.AddedDate, Articles.AddedBy, Articles.CategoryID, Articles.Title, Articles.Abstract, Articles.Body, " & _
            "Articles.Country, Articles.State, Articles.City, Articles.ReleaseDate, Articles.ExpireDate, Articles.Approved, " & _
            "Articles.Listed, Articles.CommentsEnabled, Articles.OnlyForMembers, Articles.ViewCount, Articles.Votes, Articles.TotalRating, " & _
            "ArticleCategories.Title AS CategoryTitle " & _
            "FROM Articles INNER JOIN " & _
            "ArticleCategories ON Articles.CategoryID = ArticleCategories.CategoryID " & _
            "WHERE Articles.ArticleID = ? AND Articles.LogicalDeleteInd = ?"
            DAP.AddParameter("@ArticleID", articleID, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
            Dim reader As IDataReader = DAP.ExecuteReader(strSQL)
            If reader.Read() Then
                Return GetArticleFromReader(reader, True)
            Else
                Return Nothing
            End If
        Else
            Return Nothing
        End If
    End Function

    ' Retrieves the body for the article with the specified ID
    Public Overrides Function GetArticleBody(ByVal articleID As String) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT Body FROM Articles WHERE ArticleID = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@ArticleID", articleID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return CStr(DAP.ExecuteScalar(strSQL))
    End Function

    ' Deletes an article
    Public Overrides Function DeleteArticle(ByVal articleID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "UPDATE Articles SET LogicalDeleteInd = ? WHERE ArticleID = ?"
        DAP.AddParameter("@LogicalDeleteInd", cAccessTrueValue, DbType.String)
        DAP.AddParameter("@ArticleID", articleID, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    ' Inserts a new article
    Public Overrides Function InsertArticle(ByVal article As DataTypes.ArticleDetails) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sNewArticleId As String = System.Guid.NewGuid.ToString
        Dim strSQL As String = "INSERT INTO Articles" & _
        "(AddedDate, AddedBy, CategoryID, Title, Abstract, Body, Country, State, City, " & _
        "ReleaseDate, ExpireDate, Approved, Listed, CommentsEnabled, OnlyForMembers, ArticleId, LogicalDeleteInd, ViewCount, Votes, TotalRating) " & _
        "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        DAP.AddParameter("@AddedDate", article.AddedDate, DbType.DateTime)
        DAP.AddParameter("@AddedBy", article.AddedBy, DbType.String)
        DAP.AddParameter("@CategoryID", article.CategoryID, DbType.String)
        DAP.AddParameter("@Title", article.Title, DbType.String)
        DAP.AddParameter("@Abstract", article.Abstract, DbType.String)
        DAP.AddParameter("@Body", article.Body, DbType.String)
        DAP.AddParameter("@Country", article.Country, DbType.String)
        DAP.AddParameter("@State", article.State, DbType.String)
        DAP.AddParameter("@City", article.City, DbType.String)
        DAP.AddParameter("@ReleaseDate", article.ReleaseDate, DbType.DateTime)
        DAP.AddParameter("@ExpireDate", article.ExpireDate, DbType.DateTime)
        DAP.AddParameter("@Approved", CBooleanToString(article.Approved), DbType.String)
        DAP.AddParameter("@Listed", CBooleanToString(article.Listed), DbType.String)
        DAP.AddParameter("@CommentsEnabled", CBooleanToString(article.CommentsEnabled), DbType.String)
        DAP.AddParameter("@OnlyForMembers", CBooleanToString(article.OnlyForMembers), DbType.String)
        DAP.AddParameter("@ArticleID", sNewArticleId, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        DAP.AddParameter("@ViewCount", 0, DbType.Int64)
        DAP.AddParameter("@Votes", 0, DbType.Int64)
        DAP.AddParameter("@TotalRating", 0, DbType.Int64)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return sNewArticleId
    End Function

    ' Updates an article
    Public Overrides Function UpdateArticle(ByVal article As DataTypes.ArticleDetails) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "UPDATE Articles" & _
        "SET CategoryID = ?, " & _
        "Title = ?, " & _
        "Abstract = ?, " & _
        "Body = ?, " & _
        "Country = ?, " & _
        "State = ?, " & _
        "City = ?, " & _
        "ReleaseDate = ?, " & _
        "ExpireDate = ?, " & _
        "Approved = ?, " & _
        "Listed = ?, " & _
        "CommentsEnabled = ?, " & _
        "OnlyForMembers = ? " & _
        "WHERE ArticleID = ?"
        DAP.AddParameter("@ArticleID", article.ArticleID, DbType.String)
        DAP.AddParameter("@CategoryID", article.CategoryID, DbType.String)
        DAP.AddParameter("@Title", article.Title, DbType.String)
        DAP.AddParameter("@Abstract", article.Abstract, DbType.String)
        DAP.AddParameter("@Body", article.Body, DbType.String)
        DAP.AddParameter("@Country", article.Country, DbType.String)
        DAP.AddParameter("@State", article.State, DbType.String)
        DAP.AddParameter("@City", article.City, DbType.String)
        DAP.AddParameter("@ReleaseDate", article.ReleaseDate, DbType.DateTime)
        DAP.AddParameter("@ExpireDate", article.ExpireDate, DbType.DateTime)
        DAP.AddParameter("@Approved", article.Approved, DbType.String)
        DAP.AddParameter("@Listed", article.Listed, DbType.String)
        DAP.AddParameter("@CommentsEnabled", article.CommentsEnabled, DbType.String)
        DAP.AddParameter("@OnlyForMembers", article.OnlyForMembers, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    ' Approves an article
    Public Overrides Function ApproveArticle(ByVal articleID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "UPDATE Articles SET Approved = ? WHERE ArticleID = ?"
        DAP.AddParameter("@Approved", cAccessTrueValue, DbType.String)
        DAP.AddParameter("@ArticleID", articleID, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    ' Increments the ViewCount of the specified article
    Public Overrides Function IncrementArticleViewCount(ByVal articleID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "UPDATE Articles SET ViewCount = ViewCount + 1 " & _
        "WHERE ArticleID = ?"
        DAP.AddParameter("@ArticleID", articleID, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    ' Inserts a vote for the specified article
    Public Overrides Function RateArticle(ByVal articleID As String, ByVal rating As Integer) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "UPDATE Articles " & _
        "SET Votes = Votes + 1, TotalRating = TotalRating + @Rating " & _
        "WHERE ArticleID = @ArticleID"
        DAP.AddParameter("@ArticleID", articleID, DbType.Int16)
        DAP.AddParameter("@Rating", rating, DbType.Int16)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    ' Retrieves all comments
    Public Overloads Overrides Function GetComments(ByVal pageIndex As Integer, ByVal pageSize As Integer) As List(Of DataTypes.CommentDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT * FROM " & _
        "(SELECT ArticleComments.CommentID, ArticleComments.AddedDate, ArticleComments.AddedBy, ArticleComments.AddedByEmail, ArticleComments.AddedByIP, ArticleComments.ArticleID, ArticleComments.Body, " & _
        "Articles.Title AS ArticleTitle, " & _
        "ROW_NUMBER() OVER (ORDER BY ArticleComments.AddedDate DESC) AS RowNum " & _
        "FROM ArticleComments INNER JOIN " & _
        "Articles ON ArticleComments.ArticleID = Articles.ArticleID) Comments " & _
        "WHERE ArticleComments.RowNum BETWEEN (?*?+1) AND ((?+1)*?) " & _
        "ORDER BY Comments.AddedDate DESC AND LogicalDeleteInd = ?"
        DAP.AddParameter("@PageIndex", pageIndex, DbType.Int16)
        DAP.AddParameter("@PageSize", pageSize, DbType.Int16)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return GetCommentCollectionFromReader(DAP.ExecuteReader(strSQL))
    End Function

    ' Returns the total number of comments
    Public Overloads Overrides Function GetCommentCount() As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT COUNT(*) FROM ArticleComments WHERE LogicalDeleteInd = ?"
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return CInt(DAP.ExecuteScalar(strSQL))
    End Function

    ' Retrieves all comments for the specified article
    Public Overloads Overrides Function GetComments(ByVal articleID As String, ByVal pageIndex As Integer, ByVal pageSize As Integer) As List(Of DataTypes.CommentDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT * FROM " & _
        "(SELECT ArticleComments.CommentID, ArticleComments.AddedDate, ArticleComments.AddedBy, ArticleComments.AddedByEmail, ArticleComments.AddedByIP, ArticleComments.ArticleID, ArticleComments.Body," & _
        "Articles.Title AS ArticleTitle, " & _
        "ROW_NUMBER() OVER (ORDER BY ArticleComments.AddedDate DESC) AS RowNum " & _
        "FROM ArticleComments INNER JOIN " & _
        "Articles ON ArticleComments.ArticleID = Articles.ArticleID " & _
        "WHERE ArticleComments.ArticleID = ?) ArticleComments " & _
        "WHERE ArticleComments.RowNum BETWEEN (?*?+1) AND ((?+1)*?) AND LogicalDeleteInd = ?" & _
        "ORDER BY ArticleComments.AddedDate DESC"
        DAP.AddParameter("@ArticleID", articleID, DbType.Int16)
        DAP.AddParameter("@PageIndex", pageIndex, DbType.Int16)
        DAP.AddParameter("@PageSize", pageSize, DbType.Int16)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return GetCommentCollectionFromReader(DAP.ExecuteReader(strSQL))
    End Function

    ' Returns the total number of comments for the specified article
    Public Overloads Overrides Function GetCommentCount(ByVal articleID As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT COUNT(*) FROM ArticleComments WHERE ArticleID = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@ArticleID", articleID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Return CInt(DAP.ExecuteScalar(strSQL))
    End Function

    ' Retrieves the comment with the specified ID
    Public Overrides Function GetCommentByID(ByVal commentID As String) As DataTypes.CommentDetails
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "SELECT ArticleComments.CommentID, ArticleComments.AddedDate, ArticleComments.AddedBy, ArticleComments.AddedByEmail, ArticleComments.AddedByIP, ArticleComments.ArticleID, ArticleComments.Body, " & _
        "Articles.Title AS ArticleTitle " & _
        "FROM ArticleComments INNER JOIN " & _
        "Articles ON ArticleComments.ArticleID = Articles.ArticleID " & _
        "WHERE CommentID = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@CommentID", commentID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Dim reader As IDataReader = DAP.ExecuteReader(strSQL)
        If reader.Read() Then
            Return GetCommentFromReader(reader)
        Else
            Return Nothing
        End If
    End Function

    ' Deletes a comment
    Public Overrides Function DeleteComment(ByVal commentID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "UPDATE ArticleComments SET LogicalDeleteInd = ? WHERE CommentID = ?"
        DAP.AddParameter("@LogicalDeleteInd", cAccessTrueValue, DbType.String)
        DAP.AddParameter("@CommentID", commentID, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    ' Inserts a new comment
    Public Overrides Function InsertComment(ByVal comment As DataTypes.CommentDetails) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sNewCommentId As String = System.Guid.NewGuid.ToString
        Dim strSQL As String = "INSERT INTO ArticleComments " & _
        "(AddedDate, AddedBy, AddedByEmail, AddedByIP, ArticleID, Body, LogicalDeleteInd) " & _
        "VALUES (?, ?, ?, ?, ?, ?, ?)"
        DAP.AddParameter("@AddedDate", comment.AddedDate, DbType.DateTime)
        DAP.AddParameter("@AddedBy", comment.AddedBy, DbType.String)
        DAP.AddParameter("@AddedByEmail", comment.AddedByEmail, DbType.String)
        DAP.AddParameter("@AddedByIP", comment.AddedByIP, DbType.String)
        DAP.AddParameter("@ArticleID", comment.ArticleID, DbType.Int16)
        DAP.AddParameter("@Body", comment.Body, DbType.String)
        DAP.AddParameter("@CommentID", sNewCommentId, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return CInt(sNewCommentId)
    End Function

    ' Updates a comment
    Public Overrides Function UpdateComment(ByVal comment As DataTypes.CommentDetails) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSQL As String = "UPDATE ArticleComments" & _
        "SET Body = @Body " & _
        "WHERE CommentID = ?"
        DAP.AddParameter("@CommentID", comment.ID, DbType.String)
        DAP.AddParameter("@Body", comment.Body, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(strSQL)
        Return (ret = 1)
    End Function

    Private Function CBooleanToString(ByVal input As String) As String
        If input Then
            Return "Y"
        Else
            Return "N"
        End If
    End Function
End Class

